package com.cn.hnust.utils;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletOutputStream;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;


/**
 * 
 * @ClassName: ExpExcelUtils
 * @Description: TODO(导出Excel通用类)
 * @author ht_wangjun
 * @date 2015年9月21日 下午4:55:32
 * @version V1.0
 */
public class ExpExcelUtils {

	/**
	 * @throws IOException 
	 * @param <T>
	 * 
	* @Title: export 
	* @Description: TODO(这里用一句话描述这个方法的作用) 
	* @param @param sheetName sheet名称
	* @param @param dataList 导出的数据
	* @param @param classParam 需要导出的字段
	* @param @param headers Excel标题
	* @param @param o    导出数据类型 
	* @return void    返回类型 
	* @throws
	 */
	public static void export(String sheetName, List<?> dataList, String[] classParam, String[] headers, ServletOutputStream outputStream) throws IOException {
		
		XSSFWorkbook wb = initWorkbook();
		CellStyle style = getCellStyle(wb);
		CellStyle style2 = getCellStyle2(wb);
		Sheet sheet = getSheets(wb, sheetName);
		
		//标题列
		Row rowHeader = getRows(sheet, 0);
		//标题列填充
		for (int i = 0; i < headers.length; i++) {  
        	 Cell cellHeader = rowHeader.createCell(i);
        	 cellHeader.setCellStyle(style);  
             cellHeader.setCellValue(headers[i]);  
        }
		
		//内容填充
		for(int i = 0; i < dataList.size(); i++) {  
            
			 Row row = sheet.createRow(i+1); 
		     
			 for(int j = 0; j < classParam.length; j++) {
				 Cell cell = row.createCell(j); 
				 cell.setCellStyle(style2);
				 Object obj = ClassUtils.invokeGet(dataList.get(i), classParam[j]);
				 if(obj!=null)
					 cell.setCellValue(obj+""); 
				 else
					 cell.setCellValue(""); 
			 } 
		        
		 }
		
		wb.write(outputStream);  
		outputStream.close();
	}
	//导出excel
	public static void export2(String sheetName, List<List<Object>> dataList, String[] headers, ServletOutputStream outputStream) throws IOException {
		
		XSSFWorkbook wb = initWorkbook();
		CellStyle style = getCellStyle(wb);
		CellStyle style2 = getCellStyle2(wb);
		Sheet sheet = getSheets(wb, sheetName);
		
		//标题列
		Row rowHeader = getRows(sheet, 0);
		//标题列填充
		for (int i = 0; i < headers.length; i++) {  
        	 Cell cellHeader = rowHeader.createCell(i);
        	 cellHeader.setCellStyle(style);  
             cellHeader.setCellValue(headers[i]);  
        }
		
		//内容填充
		for(int i = 0; i < dataList.size(); i++) {
            
			 Row row = sheet.createRow(i+1); 
		     
			 for(int j = 0; j < dataList.get(i).size(); j++) {
				 Cell cell = row.createCell(j); 
				 cell.setCellStyle(style2);
				 Object cellText = dataList.get(i).get(j);
				 if(cellText!=null)
					 cell.setCellValue(cellText+""); 
				 else
					 cell.setCellValue(""); 
			 } 
		 }
		
		wb.write(outputStream);  
		outputStream.close();
	}
	//多sheet导出
	public static void exportMultiSheet(List<String> sheetName, List<List<?>> dataList, List<List<String>> headers, ServletOutputStream outputStream) throws IOException {
		
		XSSFWorkbook wb = initWorkbook();
		CellStyle style = getCellStyle(wb);
		CellStyle style2 = getCellStyle2(wb);
		
		for (int i=0; i<sheetName.size(); i++) {
			String sheet_name = sheetName.get(i);
			Sheet sheet = getSheets(wb, sheet_name);
			//标题列
			Row rowHeader = getRows(sheet, 0);
			
			//标题列填充
			for (int j = 0; j < headers.get(i).size(); j++) {
				Cell cellHeader = rowHeader.createCell(j);
				cellHeader.setCellStyle(style);  
				cellHeader.setCellValue(headers.get(i).get(j));  
			}
			
			//内容填充
			for(int k = 0; k < dataList.get(i).size(); k++) {	//行数
				
				Row row = sheet.createRow(k+1); 
				
//				for(int j = 0; j < headers.get(i).size(); j++) {	//单元格内容 
					
					
					if(dataList.get(i).get(k)!=null && (dataList.get(i).get(k)) instanceof List){
						@SuppressWarnings("unchecked")
						List<String> cellList = (List<String>) dataList.get(i).get(k);
						for (int l = 0; l < cellList.size(); l++) {
							Cell cell = row.createCell(l); 
							cell.setCellStyle(style2);
							if(cellList.get(l)==null){
								cell.setCellValue(""); 
							}else{
								cell.setCellValue(cellList.get(l)); 
							}
						}
					}
//				} 
				
			}
		}
		wb.write(outputStream);  
		outputStream.close();
	}

	/**
	 * 
	* @Title: initWbStyles 
	* @Description: TODO(初始化workbook) 
	* @param @return    设定文件 
	* @return XSSFWorkbook    返回类型 
	* @throws
	 */
	private static XSSFWorkbook initWorkbook() {
		XSSFWorkbook wb = new XSSFWorkbook();
		return wb;
	}
	
	/**
	 * 
	* @Title: getCellStyle 
	* @Description: TODO(设置样式1) 
	* @param @param wb
	* @param @return    设定文件 
	* @return CellStyle    返回类型 
	* @throws
	 */
	private static CellStyle getCellStyle(XSSFWorkbook wb) {
		CellStyle style = wb.createCellStyle();
		// 设置这些样式
		style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

		Font font = wb.createFont();
		font.setColor(HSSFColor.VIOLET.index);
		font.setFontHeightInPoints((short) 12);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		// 把字体应用到当前的样式
		style.setFont(font);
		
		return style;
	}
	
	/**
	 * 
	* @Title: getCellStyle2 
	* @Description: TODO(设置样式2) 
	* @param @param wb
	* @param @return    设定文件 
	* @return CellStyle    返回类型 
	* @throws
	 */
	private static CellStyle getCellStyle2(XSSFWorkbook wb) {
		CellStyle style2 = wb.createCellStyle();

		style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
		style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		// 生成另一个字体
		Font font2 = wb.createFont();
		font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		// 把字体应用到当前的样式
		style2.setFont(font2);
		return style2;
	}
	
	/**
	 * 
	* @Title: getSheets 
	* @Description: TODO(新建一个sheet) 
	* @param @param wb
	* @param @param sheetName
	* @param @return    设定文件 
	* @return Sheet    返回类型 
	* @throws
	 */
	private static Sheet getSheets(XSSFWorkbook wb, String sheetName) {
		Sheet sh = wb.createSheet(sheetName);
		sh.setDefaultColumnWidth((int) 20);
		
		return sh;
	}
	
	/**
	 * 
	* @Title: getRows 
	* @Description: TODO(新建sheet里的row) 
	* @param @param sh
	* @param @param rowNum
	* @param @return    设定文件 
	* @return Row    返回类型 
	* @throws
	 */
	private static Row getRows(Sheet sh, int rowNum) {
		Row rowHeader = sh.createRow(rowNum);
		return rowHeader;
	}
	
	/**
	 * @throws ClassNotFoundException 
	 * @throws IllegalAccessException 
	 * @throws InstantiationException 
	 * @throws IOException 
	 * 
	* @Title: impExcelValue 
	* @Description: TODO(导入通用方法) 
	* @param @param multfile
	* @param @param classParam
	* @param @param t
	* @param @return    设定文件 
	* @return List<T>    返回类型 
	* @throws
	 */
	public static List<?> impExcelValue(MultipartFile multfile, String[] classParam, String className) 
			throws IOException, InstantiationException, IllegalAccessException, ClassNotFoundException {
		InputStream is = multfile.getInputStream();
		XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
		List<Object> list = new ArrayList<Object>();
		// 循环sheet个数
		for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
			XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
			if (xssfSheet == null) {
				continue;
			}
			//获取每个sheet中的数据
			getSheet4Obj(xssfSheet, classParam, list, className);
		}
		
		return list;
	}
	
	private static void getSheet4Obj(XSSFSheet xssfSheet, String[] classParam, List<Object> list, String className) 
			throws InstantiationException, IllegalAccessException, ClassNotFoundException {
		for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
			XSSFRow xssfRow = xssfSheet.getRow(rowNum);
			if (xssfRow == null) {
				continue;
			}

			
			Object obj=null;
			if(!"java.util.Map".equals(className))
			{
				Class<?> clzz = Class.forName(className);				
				obj=clzz.newInstance();
			}else
			{
				obj=new HashMap<String,Object>();
			}
			for(int cellNum = 0; cellNum < classParam.length; cellNum++) {
				XSSFCell value = xssfRow.getCell(cellNum);
				
				if(!"java.util.Map".equals(className))
				{					
					String valueStr = getValue(value);
					ClassUtils.invokeSet(obj, classParam[cellNum], valueStr);
				}else
				{
					((Map<String, Object>)obj).put(classParam[cellNum], value);
				}
				
			}


			
			/*Class<?> clzz = Class.forName(className);
			Object obj=null;
			if(!"java.util.Map".equals(className))
			{
				Class<?> clzz = Class.forName(className);				
				obj=clzz.newInstance();
			}else
			{
				obj=new HashMap<String,Object>();
			}
			for(int cellNum = 0; cellNum < classParam.length; cellNum++) {
				XSSFCell value = xssfRow.getCell(cellNum);
				
				if(!"java.util.Map".equals(className))
				{					
					String valueStr = getValue(value);
					ClassUtils.invokeSet(obj, classParam[cellNum], valueStr);
				}else
				{
					((Map<String, Object>)obj).put(classParam[cellNum], value);
				}
				
			}

			
			/*Class<?> clzz = Class.forName(className);
			Object obj=clzz.newInstance();
			for(int cellNum = 0; cellNum < classParam.length; cellNum++) {
				XSSFCell value = xssfRow.getCell(cellNum);
				String valueStr = getValue(value);
				ClassUtils.invokeSet(obj, classParam[cellNum], valueStr);
			}*/
			
			list.add(obj);
		}
	}
	
	@SuppressWarnings("static-access")
	private static String getValue(XSSFCell xssfRow) {
		
		if(xssfRow==null){
			return "";
		}else{
			if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
				return String.valueOf(xssfRow.getBooleanCellValue());
			} else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
				return String.valueOf(xssfRow.getNumericCellValue());
			} else {
				return String.valueOf(xssfRow.getStringCellValue());
			}
		}
		
	}
}